Delete an external data range

Click the worksheet that contains the external data range you want to delete.
On the formula bar, click the arrow next to the Name box, and then click the name of the external data range you want to delete.
To delete the external data range or the entire worksheet including the external data range but not the underlying query, click Delete or Delete Sheet on the Edit menu.
If you press DELETE on the keyboard instead of clicking Delete on the Edit menu, Microsoft Excel clears the external data range and prompts you to choose whether to delete the underlying query.

You can create a report template that removes the external data range but retains the underlying query on a worksheet when you close it.